import java.lang.annotation.Annotation;
import java.util.*;
import java.lang.reflect.*;

public class SqlUtilImpl implements SqlUtil
{
    private List<Object> objFields = new ArrayList<>();
    private List<Annotation> objAnnotations = new ArrayList<>();
    private List<Annotation> fieldAnnotations = new ArrayList<>();
    private int userID;
    
    private void getInfo(Object object)
    {
        Class clazz =  object.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++)
        {
            fields[i].setAccessible(true);
            try
            {
                objFields.add(fields[i].get(object));
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
        objAnnotations.addAll(Arrays.asList(clazz.getDeclaredAnnotations()));
        for (int i = 0; i < fields.length; i++)
        {
            Annotation[] annotations = fields[i].getDeclaredAnnotations();
            boolean hasColumn = false;
            for (int j = 0; j < annotations.length; j++)
            {
                if (annotations[j] instanceof Column)
                {
                    fieldAnnotations.add(annotations[j]);
                    hasColumn = true;
                    break;
                }
            }
            if (!hasColumn)
            {
                fieldAnnotations.add(null);
            }
        }
    }

    private void clearInfo()
    {
        objFields.clear();
        objAnnotations.clear();
        fieldAnnotations.clear();
    }

    private String[] getColumns(Object object)
    {
        getInfo(object);
        String[] columns = new String[objFields.size()];
        for (int i = 0; i < fieldAnnotations.size(); i++)
        {
            if (fieldAnnotations.get(i) != null)
            {
                Column _column = (Column) fieldAnnotations.get(i);
                columns[i] = _column.value();
            }
            else
            {
                columns[i] = null;
            }
        }
        return columns;
    }

    private String formatConstraint(String sql, int sql_original_length)
    {
        String command[] = sql.split("[ ]+");
        sql = "";
        for (int i = 0; i < sql_original_length; i++)
        {
            sql += command[i] + " ";
        }
        if (command.length > sql_original_length)
        {
            sql += "WHERE ";
            for (int i = sql_original_length; i < command.length; i += 3)
            {
                for (int j = i; j < i + 3; j++)
                {
                    sql += command[j] + " ";
                }
                sql += "AND ";
            }
            sql = sql.substring(0, sql.length() - 4);
        }
        sql = sql.substring(0, sql.length() - 1);
        return sql;
    }

    private String addConstraint(String sql, String[] columns, boolean isUpdate)
    {
        for (int i = 0; i < objFields.size(); i++)
        {
            if (columns[i] == null)
                continue;
            Object field = objFields.get(i);
            if (field != null)
            {
                if (field instanceof String)
                {
                    sql +=  " `" + columns[i] + "`" + " LIKE " + "'" + field + "'";
                }
                if (!(field instanceof String))
                {
                    if (field instanceof Number)
                    {
                        if (isUpdate && columns[i].equals("id"))
                        {
                            userID = (int) field;
                            continue;
                        }
                        if (!((Number)field).equals(0))
                            sql +=  " `" + columns[i] + "`" + " = " + field;
                    }
                    else
                    {
                        sql +=  " `" + columns[i] + "`" + " = " + "'" + field + "'";
                    }
                }
            }
        }
        return sql;
    }

    private String createSql(String sql, String[] columns, boolean isUpdate)
    {
        int sql_original_length = sql.split("[ ]+").length;
        sql = addConstraint(sql, columns, isUpdate);
        sql = formatConstraint(sql, sql_original_length);
        return sql;
    }

    @Override
    public String query(User user)
    {
        if (user == null)
            return null;
        String[] columns = getColumns(user);
        String table = ((Table) objAnnotations.get(0)).value();
        String sql = "SELECT * FROM " + "`" + table + "`";
        sql = createSql(sql, columns, false);
        clearInfo();
        return sql;
    }

    @Override
    public String insert(User user)
    {
        List<User> users = new ArrayList<>();
        users.add(user);
        return insert(users);
    }

    @Override
    public String insert(List<User> users)
    {
        if (users.size() == 0)
            return null;
        List<List<FieldIndex>> fieldIndexList = new ArrayList<>();
        String[] columns = getColumns(users.get(0));
        String table = ((Table) objAnnotations.get(0)).value();
        clearInfo();
        Set<String> availableFields = new HashSet<>();
        for (int i = 0; i < users.size(); i++)
        {
            getInfo(users.get(i));
            List<FieldIndex> fieldIndices = new ArrayList<>();
            for (int j = 0; j < objFields.size(); j++)
            {
                if (columns[j] == null)
                    continue;
                Object field = objFields.get(j);
                if (field != null)
                {
                    boolean flag = true;
                    if (field instanceof Number)
                    {
                        if (((Number)field).equals(0))
                            flag = false;
                    }
                    if (flag)
                    {
                        fieldIndices.add(new FieldIndex(field, j));
                        availableFields.add(columns[j]);
                    }
                }
            }
            fieldIndexList.add(fieldIndices);
            clearInfo();
        }
        String sql = "INSERT INTO " + "`" + table + "` (";
        List<Integer> availableIndex = new ArrayList<>();
        for (int i = 0; i < columns.length; i++)
        {
            if (columns[i] == null)
                continue;
            if (availableFields.contains(columns[i]))
            {
                sql += "`" + columns[i] + "`, ";
                availableIndex.add(i);
            }
        }
        sql = sql.substring(0, sql.length() - 2);
        sql += ") VALUES ";
        for (int i = 0; i < users.size(); i++)
        {
            sql += "(";
            List<FieldIndex> fieldIndices = fieldIndexList.get(i);
            int index = 0;
            for (int j = 0; j < availableIndex.size(); j++)
            {
                if (index == fieldIndices.size())
                    break;
                if (fieldIndices.get(index).index != availableIndex.get(j))
                {
                    sql += " ";
                }
                else
                {
                    Object field = fieldIndices.get(index).field;
                    if (field instanceof Number)
                    {
                        sql += field;
                    }
                    else
                    {
                        sql += "'" + field + "'";
                    }
                    index++;
                }
                if (j != availableIndex.size() - 1)
                {
                    sql += ", ";
                }
            }
            sql += ")";
            if (i != users.size() - 1)
            {
                sql += ", ";
            }
        }
        return sql;
    }

    @Override
    public String delete(User user)
    {
        if (user == null)
            return null;
        String[] columns = getColumns(user);
        String table = ((Table) objAnnotations.get(0)).value();
        String sql = "DELETE FROM " + "`" + table + "`";
        sql = createSql(sql, columns, false);
        clearInfo();
        return sql;
    }

    @Override
    public String update(User user)
    {
        if (user == null)
            return null;
        String[] columns = getColumns(user);
        String table = ((Table) objAnnotations.get(0)).value();
        String sql = "UPDATE `" + table + "` SET ";
        sql = createSql(sql, columns, true);
        sql += " WHERE `id` = " + userID;
        clearInfo();
        return sql;
    }
}